package dao.impl;

import dao.StudentDao;
import domain.Student;
import util.JdbcUtils;
import util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 于金鹏
 * @Date 2024/12/16 13:21
 * @Package StudentCheckSystem
 *
 * 学生数据访问实现类
 */
public class StudentDaoImpl implements StudentDao {

    @Override
    public List<Student> getAllStudents(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select s.*,mc1.name as majorName , mc2.name as className " +
                "from student s " +
                "left join majors_classes mc1 on s.major_id = mc1.id " +
                "left join majors_classes mc2 on s.class_id = mc2.id " +
                "where 1 = 1  ";
        // 组装查询条件，根据专业id查询
        if (student != null && student.getMajorId() > 0) {
            sql += " and s.major_id =?";
        }
        // 组装查询条件，根据班级id查询
        if (student != null && student.getClassId() > 0) {
            sql += " and s.class_id =?";
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置专业id条件的值（如果有）
        if (student != null && student.getMajorId() > 0) {
            preparedStatement.setInt(i++, student.getMajorId());
        }
        // 设置班级id条件的值（如果有）
        if (student != null && student.getClassId() > 0) {
            preparedStatement.setInt(i++, student.getClassId());
        }
        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        List<Student> list = getStudentsFromResultSet(rs);

        preparedStatement.close();
        conn.close();
        return list;
    }

    @Override
    public List<Student> page(Student student, int pageNum, int pageSize) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql = "select s.*,mc1.name as majorName , mc2.name as className " +
                "from student s " +
                "left join majors_classes mc1 on s.major_id = mc1.id " +
                "left join majors_classes mc2 on s.class_id = mc2.id " +
                "where 1 = 1 ";
        // 组装查询条件，根据专业名称查询
        if (StringUtils.isNotBlank(student.getMajorName())){
            sql += " and mc1.name like ? ";
        }
        // 组装查询条件，根据班级名称查询
        if (StringUtils.isNotBlank(student.getClassName())) {
            sql += " and mc2.name like ?  ";
        }

        sql += " limit ?,?";

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置专业id条件的值（如果有）
        if (StringUtils.isNotBlank(student.getMajorName())) {
            preparedStatement.setString(i++, "%"+student.getMajorName()+"%");
        }
        // 设置班级id条件的值（如果有）
        if (StringUtils.isNotBlank(student.getClassName())) {
            preparedStatement.setString(i++, "%"+student.getClassName()+"%");
        }
        // 设置分页参数
        preparedStatement.setInt(i++, (pageNum - 1) * pageSize);
        preparedStatement.setInt(i++, pageSize);
        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        List<Student> list = getStudentsFromResultSet(rs);
        preparedStatement.close();
        conn.close();
        return list;
    }

    @Override
    public int count(Student student) throws SQLException {
        Connection conn = JdbcUtils.getConnection();

        String sql ="select count(*) as total " +
                "from student s " +
                "left join majors_classes mc1 on s.major_id = mc1.id " +
                "left join majors_classes mc2 on s.class_id = mc2.id " +
                "where 1 = 1 ";
        // 组装查询条件，根据专业名称查询
        if (StringUtils.isNotBlank(student.getMajorName())){
            sql += " and mc1.name like ? ";
        }
        // 组装查询条件，根据班级名称查询
        if (StringUtils.isNotBlank(student.getClassName())) {
            sql += " and mc2.name like ?  ";
        }

        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        // 设置专业名称条件的值（如果有）
        if (StringUtils.isNotBlank(student.getMajorName())) {
            preparedStatement.setString(i++, "%"+student.getMajorName()+"%");
        }
        // 设置班级名称条件的值（如果有）
        if (StringUtils.isNotBlank(student.getClassName())) {
            preparedStatement.setString(i++, "%"+student.getClassName()+"%");
        }

        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 封装结果
        int total = 0;
        while (rs.next()) {
            total = rs.getInt("total");
        }
        preparedStatement.close();
        conn.close();
        return total;
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        // 获取数据库连接
        Connection connection = JdbcUtils.getConnection();
        // 编写SQL语句
        String sql = "INSERT INTO student (student_id, student_name, major_id, class_id, student_password) VALUES (?, ?, ?, ?, ?)";
        // 创建PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 设置参数
        int i = 1;
        preparedStatement.setString(i++, student.getStudentId());
        preparedStatement.setString(i++, student.getStudentName());
        preparedStatement.setInt(i++, student.getMajorId());
        preparedStatement.setInt(i++, student.getClassId());
        preparedStatement.setString(i++, student.getStudentPassword());
        int result = preparedStatement.executeUpdate();
        // 关闭资源
        preparedStatement.close();
        connection.close();
        return result;
    }

    @Override
    public int updateStudent(Student student) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "UPDATE student SET student_id = ?, student_name = ?, major_id = ?, class_id = ?, student_password = ? WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        int i = 1;

        preparedStatement.setString(i++, student.getStudentId());
        preparedStatement.setString(i++, student.getStudentName());
        preparedStatement.setInt(i++, student.getMajorId());
        preparedStatement.setInt(i++, student.getClassId());
        preparedStatement.setString(i++, student.getStudentPassword());
        preparedStatement.setInt(i++, student.getId());
        int result = preparedStatement.executeUpdate();
        // 关闭资源
        preparedStatement.close();
        connection.close();
        return result;
    }

    @Override
    public int deleteStudent(int id) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        String sql = "DELETE FROM student WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        int result = preparedStatement.executeUpdate();
        // 关闭资源
        preparedStatement.close();
        connection.close();
        return result;
    }

    @Override
    public Student getStudentById(int id) throws SQLException {
        // 获取连接
        Connection conn = JdbcUtils.getConnection();
        // 编写sql
        String sql = "select s.*,mc1.name as majorName , mc2.name as className " +
                "from student s " +
                "left join majors_classes mc1 on s.major_id = mc1.id " +
                "left join majors_classes mc2 on s.class_id = mc2.id " +
                "where s.id = ? ";
        // 创建sql执行对象
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        // 设置参数
        preparedStatement.setInt(1, id);
        // 执行sql
        ResultSet rs = preparedStatement.executeQuery();
        // 调用封装获取结果
        List<Student> list = getStudentsFromResultSet(rs);
        // 关闭连接
        preparedStatement.close();
        conn.close();
        // 判断有没有查到
        if (!list.isEmpty()) {
            // 返回查询到的内容
            return list.get(0);
        }
        // 没查到返回空
        return null;
    }

    private List<Student> getStudentsFromResultSet(ResultSet resultSet) throws SQLException {
        List<Student> list = new ArrayList<Student>();
        while (resultSet.next()) {
            list.add(new Student(
                    resultSet.getInt("id"),
                    resultSet.getString("student_id"),
                    resultSet.getString("student_name"),
                    resultSet.getInt("major_id"),
                    resultSet.getInt("class_id"),
                    resultSet.getString("majorName"),
                    resultSet.getString("className"),
                    resultSet.getString("student_password")
            ));
        }
        return list;
    }
}
